-- Autonomous College Database Schema
-- Created: 2024
-- Description: Complete database schema for autonomous college management system

-- Create database
CREATE DATABASE IF NOT EXISTS `autonomous_college` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `autonomous_college`;

-- Certificate Issuance Table
CREATE TABLE `certificate_issuance` (
`certificate_id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`certificate_type` enum('Degree','Provisional','Migration','Other') NOT NULL,
`issue_date` date NOT NULL,
`status` enum('Applied','Issued','Delivered') NOT NULL,
PRIMARY KEY (`certificate_id`),
KEY `idx_ci_student_id` (`student_id`),
CONSTRAINT `fk_ci_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Certificate Sequence Table
CREATE TABLE `certificate_sequence` (
`academic_year` varchar(50) NOT NULL,
`course_id` int(11) NOT NULL,
`last_number` int(11) DEFAULT 0,
PRIMARY KEY (`academic_year`,`course_id`),
KEY `course_id` (`course_id`),
CONSTRAINT `certificate_sequence_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Colleges Table
CREATE TABLE `colleges` (
`college_id` int(11) NOT NULL AUTO_INCREMENT,
`college_code` varchar(10) NOT NULL,
`college_name` varchar(255) NOT NULL,
`status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
PRIMARY KEY (`college_id`),
UNIQUE KEY `uq_college_code` (`college_code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Consolidated Folio Sequence Table
CREATE TABLE `consolidated_folio_sequence` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`seq_year` varchar(9) NOT NULL,
`course_code` varchar(20) NOT NULL,
`next_number` int(11) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_year_course` (`seq_year`,`course_code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Consolidated Marksheets Table
CREATE TABLE `consolidated_marksheets` (
`consolidated_id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`folio_number` varchar(50) NOT NULL,
`marksheet_data` longtext NOT NULL COMMENT 'JSON data of consolidated marksheet',
`generated_by` int(11) DEFAULT NULL,
`generated_date` datetime DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`consolidated_id`),
UNIQUE KEY `uq_student_course` (`student_id`,`course_id`),
UNIQUE KEY `uq_consol_folio` (`folio_number`),
KEY `course_id` (`course_id`),
KEY `generated_by` (`generated_by`),
CONSTRAINT `consolidated_marksheets_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE,
CONSTRAINT `consolidated_marksheets_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE,
CONSTRAINT `consolidated_marksheets_ibfk_3` FOREIGN KEY (`generated_by`) REFERENCES `staff` (`staff_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Courses Table
CREATE TABLE `courses` (
`course_id` int(11) NOT NULL AUTO_INCREMENT,
`college_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL,
`course_name` varchar(255) NOT NULL,
`course_code` varchar(20) NOT NULL,
`regulation_year` year(4) NOT NULL,
`duration_years` int(11) NOT NULL,
`total_semesters` int(11) NOT NULL,
`status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
PRIMARY KEY (`course_id`),
UNIQUE KEY `uq_course_code` (`course_code`),
KEY `idx_courses_college_id` (`college_id`),
KEY `idx_courses_category_id` (`category_id`),
CONSTRAINT `fk_courses_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`),
CONSTRAINT `fk_courses_college` FOREIGN KEY (`college_id`) REFERENCES `colleges` (`college_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Course Fee Structure Table
CREATE TABLE `course_fee_structure` (
`fee_structure_id` int(11) NOT NULL AUTO_INCREMENT,
`course_id` int(11) NOT NULL,
`fee_type_id` int(11) NOT NULL,
`amount` decimal(10,2) NOT NULL,
`effective_from` date NOT NULL,
`effective_to` date DEFAULT NULL,
PRIMARY KEY (`fee_structure_id`),
KEY `idx_cfs_course_id` (`course_id`),
KEY `idx_cfs_fee_type_id` (`fee_type_id`),
CONSTRAINT `fk_cfs_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`),
CONSTRAINT `fk_cfs_fee_type` FOREIGN KEY (`fee_type_id`) REFERENCES `fee_types` (`fee_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Co Po Mapping Table
CREATE TABLE `co_po_mapping` (
`mapping_id` int(11) NOT NULL AUTO_INCREMENT,
`subject_id` int(11) NOT NULL,
`co_code` varchar(10) NOT NULL,
`po_codes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`po_codes`)),
PRIMARY KEY (`mapping_id`),
KEY `idx_copo_subject_id` (`subject_id`),
CONSTRAINT `fk_copo_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Exams Table
CREATE TABLE `exams` (
`exam_id` int(11) NOT NULL AUTO_INCREMENT,
`exam_name` varchar(255) NOT NULL,
`exam_type_id` int(11) NOT NULL,
`academic_year` varchar(20) NOT NULL,
`semester` int(11) NOT NULL,
`status` enum('Draft','Announced','In Progress','Completed','Results Published') NOT NULL,
`is_arrear_allowed` tinyint(1) NOT NULL DEFAULT 1,
`mark_conversion_type` enum('Best of 2','Average of 3','Percentage (e.g., 80%)') DEFAULT NULL,
`conversion_value` decimal(5,2) DEFAULT NULL,
PRIMARY KEY (`exam_id`),
KEY `idx_exams_type` (`exam_type_id`),
KEY `idx_exams_year_semester` (`academic_year`,`semester`),
KEY `idx_exams_type_year` (`exam_type_id`,`academic_year`),
CONSTRAINT `fk_exams_exam_type` FOREIGN KEY (`exam_type_id`) REFERENCES `exam_types` (`exam_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Exam Attendance Table
CREATE TABLE `exam_attendance` (
`attendance_id` int(11) NOT NULL AUTO_INCREMENT,
`registration_id` int(11) NOT NULL,
`exam_date` date NOT NULL,
`session` enum('FN','AN') NOT NULL,
`status` enum('Present','Absent','Late') NOT NULL DEFAULT 'Present',
`remarks` text DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`attendance_id`),
UNIQUE KEY `uq_registration_date_session` (`registration_id`,`exam_date`,`session`),
KEY `idx_attendance_registration` (`registration_id`),
KEY `idx_attendance_date` (`exam_date`),
KEY `idx_attendance_status` (`status`),
CONSTRAINT `fk_attendance_registration` FOREIGN KEY (`registration_id`) REFERENCES `student_registrations` (`registration_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Exam Subjects Table
CREATE TABLE `exam_subjects` (
`exam_subject_id` int(11) NOT NULL AUTO_INCREMENT,
`exam_id` int(11) NOT NULL,
`subject_id` int(11) NOT NULL,
`max_marks` int(11) NOT NULL,
`pass_marks` int(11) NOT NULL,
`exam_date` date DEFAULT NULL,
`session` enum('FN','AN') DEFAULT NULL,
PRIMARY KEY (`exam_subject_id`),
KEY `idx_exam_subjects_exam_id` (`exam_id`),
KEY `idx_exam_subjects_subject_id` (`subject_id`),
KEY `idx_exam_subjects_date` (`exam_date`),
KEY `idx_exam_subjects_session` (`session`),
CONSTRAINT `fk_exam_subjects_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`exam_id`),
CONSTRAINT `fk_exam_subjects_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Exam Timetable Table
CREATE TABLE `exam_timetable` (
`timetable_id` int(11) NOT NULL AUTO_INCREMENT,
`exam_subject_id` int(11) NOT NULL,
`exam_date` date NOT NULL,
`session` enum('FN','AN') NOT NULL,
`start_time` time NOT NULL,
`end_time` time NOT NULL,
PRIMARY KEY (`timetable_id`),
UNIQUE KEY `uq_tt_exam_subject` (`exam_subject_id`),
CONSTRAINT `fk_tt_exam_subject` FOREIGN KEY (`exam_subject_id`) REFERENCES `exam_subjects` (`exam_subject_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Exam Types Table
CREATE TABLE `exam_types` (
`exam_type_id` int(11) NOT NULL AUTO_INCREMENT,
`type_name` varchar(50) NOT NULL,
`description` text DEFAULT NULL,
PRIMARY KEY (`exam_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- External Staff Applications Table
CREATE TABLE `external_staff_applications` (
`application_id` int(11) NOT NULL AUTO_INCREMENT,
`staff_id` int(11) NOT NULL,
`exam_id` int(11) NOT NULL,
`preferred_role` enum('Valuator','Invigilator') NOT NULL,
`application_status` enum('Applied','Approved','Rejected') NOT NULL,
`applied_date` datetime NOT NULL,
`approved_by` int(11) DEFAULT NULL,
`approved_date` datetime DEFAULT NULL,
PRIMARY KEY (`application_id`),
KEY `idx_esa_staff_id` (`staff_id`),
KEY `idx_esa_exam_id` (`exam_id`),
KEY `idx_esa_approved_by` (`approved_by`),
CONSTRAINT `fk_esa_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `staff` (`staff_id`),
CONSTRAINT `fk_esa_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`exam_id`),
CONSTRAINT `fk_esa_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fee Types Table
CREATE TABLE `fee_types` (
`fee_type_id` int(11) NOT NULL AUTO_INCREMENT,
`fee_name` varchar(100) NOT NULL,
`description` text DEFAULT NULL,
PRIMARY KEY (`fee_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Folio Sequence Table
CREATE TABLE `folio_sequence` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`academic_year` varchar(50) NOT NULL,
`course_id` int(11) NOT NULL,
`last_folio_number` int(11) DEFAULT 0,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_academic_course` (`academic_year`,`course_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Grades Table
CREATE TABLE `grades` (
`grade_id` int(11) NOT NULL AUTO_INCREMENT,
`grading_system_id` int(11) NOT NULL,
`grade_name` varchar(5) NOT NULL,
`min_marks` int(11) NOT NULL,
`max_marks` int(11) NOT NULL,
`grade_point` decimal(3,2) NOT NULL,
`is_pass` tinyint(1) NOT NULL,
PRIMARY KEY (`grade_id`),
KEY `idx_grades_system` (`grading_system_id`),
CONSTRAINT `fk_grades_system` FOREIGN KEY (`grading_system_id`) REFERENCES `grading_systems` (`grading_system_id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Grading Systems Table
CREATE TABLE `grading_systems` (
`grading_system_id` int(11) NOT NULL AUTO_INCREMENT,
`system_name` varchar(100) NOT NULL,
`course_id` int(11) NOT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`grading_system_id`),
KEY `idx_grading_systems_course_id` (`course_id`),
CONSTRAINT `fk_grading_systems_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Halls Table
CREATE TABLE `halls` (
`hall_id` int(11) NOT NULL AUTO_INCREMENT,
`college_id` int(11) NOT NULL,
`hall_name` varchar(100) NOT NULL,
`block_name` varchar(50) DEFAULT NULL,
`capacity` int(11) NOT NULL,
PRIMARY KEY (`hall_id`),
KEY `idx_halls_college_id` (`college_id`),
CONSTRAINT `fk_halls_college` FOREIGN KEY (`college_id`) REFERENCES `colleges` (`college_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Hall Allocations Table
CREATE TABLE `hall_allocations` (
`allocation_id` int(11) NOT NULL AUTO_INCREMENT,
`timetable_id` int(11) NOT NULL COMMENT 'Links to exam_timetable for specific exam session',
`hall_id` int(11) NOT NULL COMMENT 'Links to halls table for hall details',
`num_students` int(11) NOT NULL COMMENT 'Number of students allocated to this hall',
`created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'When allocation was created',
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'When allocation was last updated',
PRIMARY KEY (`allocation_id`),
KEY `idx_ha_timetable_id` (`timetable_id`),
KEY `idx_ha_hall_id` (`hall_id`),
KEY `idx_ha_num_students` (`num_students`),
CONSTRAINT `fk_ha_hall` FOREIGN KEY (`hall_id`) REFERENCES `halls` (`hall_id`) ON DELETE CASCADE,
CONSTRAINT `fk_ha_timetable` FOREIGN KEY (`timetable_id`) REFERENCES `exam_timetable` (`timetable_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Hall allocation records for exam sessions';

-- Hall Settings Table
CREATE TABLE `hall_settings` (
`hall_setting_id` int(11) NOT NULL AUTO_INCREMENT,
`hall_id` int(11) NOT NULL COMMENT 'Links to halls table',
`rows_count` int(11) NOT NULL COMMENT 'Number of rows in the hall',
`columns_count` int(11) NOT NULL COMMENT 'Number of columns in the hall',
`seating_method` enum('Parallel','Curved') NOT NULL DEFAULT 'Parallel' COMMENT 'Seating arrangement method',
`bench_capacity` int(11) DEFAULT NULL COMMENT 'Students per bench/seat',
`max_students` int(11) DEFAULT NULL COMMENT 'Maximum students that can be seated',
`row_labeling` enum('Numeric','Alphabetic') NOT NULL DEFAULT 'Numeric' COMMENT 'Row labeling system (R1,R2 or A,B,C)',
`internal_invigilators_required` int(11) NOT NULL DEFAULT 1 COMMENT 'Number of internal invigilators needed',
`external_invigilators_required` int(11) NOT NULL DEFAULT 0 COMMENT 'Number of external invigilators needed',
`aisle_every_n_cols` int(11) DEFAULT 0 COMMENT 'Create aisle every N columns (0 = no aisles)',
`aisle_every_n_rows` int(11) DEFAULT 0 COMMENT 'Create aisle every N rows (0 = no aisles)',
`created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'When settings were created',
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'When settings were last updated',
PRIMARY KEY (`hall_setting_id`),
UNIQUE KEY `uq_hall_settings_hall` (`hall_id`) COMMENT 'One setting per hall',
KEY `idx_hs_hall_id` (`hall_id`),
KEY `idx_hs_max_students` (`max_students`),
CONSTRAINT `fk_hs_hall` FOREIGN KEY (`hall_id`) REFERENCES `halls` (`hall_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Detailed hall configuration and seating layout settings';

-- Internal Ci Attendance Table
CREATE TABLE `internal_ci_attendance` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`internal_exam_id` int(11) NOT NULL,
`student_id` int(11) NOT NULL,
`attendance_pct` decimal(5,2) NOT NULL DEFAULT 0.00,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_ie_student` (`internal_exam_id`,`student_id`),
KEY `idx_ie` (`internal_exam_id`),
KEY `idx_student` (`student_id`),
CONSTRAINT `fk_ica_exam` FOREIGN KEY (`internal_exam_id`) REFERENCES `internal_exams` (`internal_exam_id`) ON DELETE CASCADE,
CONSTRAINT `fk_ica_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Internal Exams Table
CREATE TABLE `internal_exams` (
`internal_exam_id` int(11) NOT NULL AUTO_INCREMENT,
`exam_type_id` int(11) NOT NULL,
`subject_id` int(11) NOT NULL,
`academic_year` varchar(20) NOT NULL,
`semester` int(11) NOT NULL,
`max_marks` int(11) NOT NULL,
`pass_marks` int(11) NOT NULL,
`exam_date` date DEFAULT NULL,
`conducted_by` int(11) DEFAULT NULL,
PRIMARY KEY (`internal_exam_id`),
KEY `idx_ie_exam_type_id` (`exam_type_id`),
KEY `idx_ie_subject_id` (`subject_id`),
KEY `idx_ie_conducted_by` (`conducted_by`),
CONSTRAINT `fk_ie_conducted_by` FOREIGN KEY (`conducted_by`) REFERENCES `staff` (`staff_id`),
CONSTRAINT `fk_ie_exam_type` FOREIGN KEY (`exam_type_id`) REFERENCES `exam_types` (`exam_type_id`),
CONSTRAINT `fk_ie_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Internal Marks Table
CREATE TABLE `internal_marks` (
`internal_mark_id` int(11) NOT NULL AUTO_INCREMENT,
`internal_exam_id` int(11) NOT NULL,
`student_id` int(11) NOT NULL,
`marks_obtained` decimal(5,2) NOT NULL,
`attendance_status` enum('Present','Absent') NOT NULL DEFAULT 'Present',
PRIMARY KEY (`internal_mark_id`),
UNIQUE KEY `uq_im_exam_student` (`internal_exam_id`,`student_id`),
KEY `idx_im_internal_exam_id` (`internal_exam_id`),
KEY `idx_im_student_id` (`student_id`),
CONSTRAINT `fk_im_internal_exam` FOREIGN KEY (`internal_exam_id`) REFERENCES `internal_exams` (`internal_exam_id`),
CONSTRAINT `fk_im_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Internal Marks Summary Table
CREATE TABLE `internal_marks_summary` (
`summary_id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`subject_id` int(11) NOT NULL,
`academic_year` varchar(9) NOT NULL,
`semester` int(11) NOT NULL,
`internal_total` decimal(5,2) NOT NULL,
`internal_grade` varchar(5) DEFAULT NULL,
PRIMARY KEY (`summary_id`),
UNIQUE KEY `uq_ims_unique` (`student_id`,`subject_id`,`academic_year`,`semester`),
KEY `idx_ims_student_id` (`student_id`),
KEY `idx_ims_subject_id` (`subject_id`),
CONSTRAINT `fk_ims_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`),
CONSTRAINT `fk_ims_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Invigilation Duty Table
CREATE TABLE `invigilation_duty` (
`duty_id` int(11) NOT NULL AUTO_INCREMENT,
`staff_id` int(11) NOT NULL,
`timetable_id` int(11) NOT NULL,
`hall_id` int(11) NOT NULL,
`role` enum('Chief Invigilator','Invigilator') NOT NULL,
`status` enum('Assigned','Present','Absent') NOT NULL,
PRIMARY KEY (`duty_id`),
KEY `idx_id_staff_id` (`staff_id`),
KEY `idx_id_timetable_id` (`timetable_id`),
KEY `idx_id_hall_id` (`hall_id`),
CONSTRAINT `fk_id_hall` FOREIGN KEY (`hall_id`) REFERENCES `halls` (`hall_id`),
CONSTRAINT `fk_id_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`),
CONSTRAINT `fk_id_timetable` FOREIGN KEY (`timetable_id`) REFERENCES `exam_timetable` (`timetable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mark Entry Assignments Table
CREATE TABLE `mark_entry_assignments` (
`assignment_id` int(11) NOT NULL AUTO_INCREMENT,
`session_id` int(11) NOT NULL,
`valuator_id` int(11) NOT NULL,
`question_range_start` int(11) NOT NULL,
`question_range_end` int(11) NOT NULL,
`total_questions` int(11) NOT NULL,
`status` enum('Assigned','In Progress','Completed','Reviewed') NOT NULL DEFAULT 'Assigned',
`assigned_at` timestamp NOT NULL DEFAULT current_timestamp(),
`completed_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`assignment_id`),
KEY `idx_mea_session` (`session_id`),
KEY `idx_mea_valuator` (`valuator_id`),
CONSTRAINT `fk_mea_session` FOREIGN KEY (`session_id`) REFERENCES `mark_entry_sessions` (`session_id`),
CONSTRAINT `fk_mea_valuator` FOREIGN KEY (`valuator_id`) REFERENCES `staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mark Entry Progress Table
CREATE TABLE `mark_entry_progress` (
`progress_id` int(11) NOT NULL AUTO_INCREMENT,
`session_id` int(11) NOT NULL,
`valuator_id` int(11) NOT NULL,
`total_students` int(11) NOT NULL DEFAULT 0,
`students_entered` int(11) NOT NULL DEFAULT 0,
`students_reviewed` int(11) NOT NULL DEFAULT 0,
`total_questions` int(11) NOT NULL DEFAULT 0,
`questions_entered` int(11) NOT NULL DEFAULT 0,
`progress_percentage` decimal(5,2) NOT NULL DEFAULT 0.00,
`last_updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`progress_id`),
UNIQUE KEY `uq_mep_session_valuator` (`session_id`,`valuator_id`),
KEY `idx_mep_session` (`session_id`),
KEY `idx_mep_valuator` (`valuator_id`),
CONSTRAINT `fk_mep_session` FOREIGN KEY (`session_id`) REFERENCES `mark_entry_sessions` (`session_id`),
CONSTRAINT `fk_mep_valuator` FOREIGN KEY (`valuator_id`) REFERENCES `staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mark Entry Sessions Table
CREATE TABLE `mark_entry_sessions` (
`session_id` int(11) NOT NULL AUTO_INCREMENT,
`session_name` varchar(255) NOT NULL,
`exam_subject_id` int(11) NOT NULL,
`pattern_id` int(11) NOT NULL,
`academic_year` varchar(50) NOT NULL,
`exam_type_id` int(11) NOT NULL,
`session_date` date NOT NULL,
`status` enum('Draft','Active','Completed','Locked') NOT NULL DEFAULT 'Draft',
`created_by` int(11) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`session_id`),
KEY `idx_mes_exam_subject` (`exam_subject_id`),
KEY `idx_mes_pattern` (`pattern_id`),
KEY `idx_mes_created_by` (`created_by`),
CONSTRAINT `fk_mes_created_by` FOREIGN KEY (`created_by`) REFERENCES `staff` (`staff_id`),
CONSTRAINT `fk_mes_exam_subject` FOREIGN KEY (`exam_subject_id`) REFERENCES `exam_subjects` (`exam_subject_id`),
CONSTRAINT `fk_mes_pattern` FOREIGN KEY (`pattern_id`) REFERENCES `question_patterns` (`pattern_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pattern Questions Table
CREATE TABLE `pattern_questions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`section_id` int(11) NOT NULL,
`question_no` int(11) NOT NULL,
`question_text` text NOT NULL,
`co_codes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`co_codes`)),
`po_codes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`po_codes`)),
PRIMARY KEY (`id`),
KEY `idx_pq_section_id` (`section_id`),
CONSTRAINT `fk_pq_section` FOREIGN KEY (`section_id`) REFERENCES `pattern_sections` (`section_id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pattern Sections Table
CREATE TABLE `pattern_sections` (
`section_id` int(11) NOT NULL AUTO_INCREMENT,
`pattern_id` int(11) NOT NULL,
`section_name` varchar(100) NOT NULL,
`num_questions` int(11) NOT NULL,
`marks_per_question` int(11) NOT NULL,
`section_total` int(11) NOT NULL,
`question_start_num` int(11) NOT NULL,
`question_end_num` int(11) NOT NULL,
PRIMARY KEY (`section_id`),
KEY `idx_ps_pattern_id` (`pattern_id`),
CONSTRAINT `fk_ps_pattern` FOREIGN KEY (`pattern_id`) REFERENCES `question_patterns` (`pattern_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- People Sync History Table
CREATE TABLE `people_sync_history` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`source_year` varchar(20) DEFAULT NULL,
`source_class` varchar(50) DEFAULT NULL,
`source_section` varchar(50) DEFAULT NULL,
`register_number` varchar(50) NOT NULL,
`action_taken` enum('created','updated','skipped') NOT NULL,
`message` varchar(255) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_reg` (`register_number`)
) ENGINE=InnoDB AUTO_INCREMENT=179 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permission Table
CREATE TABLE `permission` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`module_id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`prefix` varchar(100) NOT NULL,
`show_view` tinyint(1) DEFAULT 1,
`show_add` tinyint(1) DEFAULT 1,
`show_edit` tinyint(1) DEFAULT 1,
`show_delete` tinyint(1) DEFAULT 1,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=169 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Permission Modules Table
CREATE TABLE `permission_modules` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`prefix` varchar(50) NOT NULL,
`system` tinyint(1) NOT NULL,
`sorted` tinyint(4) NOT NULL,
`in_module` tinyint(1) NOT NULL DEFAULT 1,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Question Paper Orders Table
CREATE TABLE `question_paper_orders` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`exam_subject_id` int(11) NOT NULL,
`setter_id` int(11) NOT NULL,
`status` enum('Order Sent','Accepted','Draft Submitted','Approved','Rejected') NOT NULL,
`time_duration` int(11) DEFAULT NULL,
`approver_id` int(11) DEFAULT NULL,
`approved_at` datetime DEFAULT NULL,
`final_pattern_id` int(11) DEFAULT NULL,
PRIMARY KEY (`order_id`),
UNIQUE KEY `uq_qpo_exam_subject` (`exam_subject_id`),
KEY `idx_qpo_setter_id` (`setter_id`),
KEY `idx_qpo_approver_id` (`approver_id`),
KEY `idx_qpo_final_pattern_id` (`final_pattern_id`),
CONSTRAINT `fk_qpo_approver` FOREIGN KEY (`approver_id`) REFERENCES `staff` (`staff_id`),
CONSTRAINT `fk_qpo_exam_subject` FOREIGN KEY (`exam_subject_id`) REFERENCES `exam_subjects` (`exam_subject_id`),
CONSTRAINT `fk_qpo_final_pattern` FOREIGN KEY (`final_pattern_id`) REFERENCES `question_patterns` (`pattern_id`),
CONSTRAINT `fk_qpo_setter` FOREIGN KEY (`setter_id`) REFERENCES `staff` (`staff_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Question Patterns Table
CREATE TABLE `question_patterns` (
`pattern_id` int(11) NOT NULL AUTO_INCREMENT,
`pattern_name` varchar(100) NOT NULL,
`total_marks` int(11) NOT NULL,
`exam_subject_id` int(11) NOT NULL,
PRIMARY KEY (`pattern_id`),
UNIQUE KEY `uq_qp_exam_subject` (`exam_subject_id`),
CONSTRAINT `fk_qp_exam_subject` FOREIGN KEY (`exam_subject_id`) REFERENCES `exam_subjects` (`exam_subject_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Result Announcements Table
CREATE TABLE `result_announcements` (
`announcement_id` int(11) NOT NULL AUTO_INCREMENT,
`course_id` int(11) NOT NULL,
`academic_year` varchar(50) NOT NULL,
`exam_type_id` int(11) NOT NULL,
`start_at` datetime NOT NULL,
`end_at` datetime NOT NULL,
`is_active` tinyint(1) NOT NULL DEFAULT 1,
`created_at` timestamp NULL DEFAULT current_timestamp(),
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`announcement_id`),
KEY `idx_course_year_type` (`course_id`,`academic_year`,`exam_type_id`,`is_active`),
CONSTRAINT `fk_ra_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Revaluation Applications Table
CREATE TABLE `revaluation_applications` (
`application_id` int(11) NOT NULL AUTO_INCREMENT,
`academic_year` varchar(50) NOT NULL,
`course_id` int(11) NOT NULL,
`student_id` int(11) NOT NULL,
`exam_id` int(11) NOT NULL,
`subject_id` int(11) NOT NULL,
`applied_on` datetime NOT NULL DEFAULT current_timestamp(),
`status` enum('Applied','Processing','Completed','Rejected') NOT NULL DEFAULT 'Applied',
`remarks` varchar(255) DEFAULT NULL,
PRIMARY KEY (`application_id`),
KEY `idx_ay_student_subject` (`academic_year`,`student_id`,`subject_id`),
KEY `fk_rev_course` (`course_id`),
KEY `fk_rev_student` (`student_id`),
KEY `fk_rev_exam` (`exam_id`),
KEY `fk_rev_subject` (`subject_id`),
CONSTRAINT `fk_rev_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE,
CONSTRAINT `fk_rev_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`exam_id`) ON DELETE CASCADE,
CONSTRAINT `fk_rev_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE,
CONSTRAINT `fk_rev_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Roles Table
CREATE TABLE `roles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`prefix` varchar(50) DEFAULT NULL,
`is_system` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Semester Results Table
CREATE TABLE `semester_results` (
`sem_result_id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`exam_id` int(11) NOT NULL,
`total_credits` int(11) NOT NULL,
`earned_credits` int(11) NOT NULL,
`sgpa` decimal(4,2) NOT NULL,
`cgpa` decimal(4,2) NOT NULL,
`result` enum('Pass','Fail','Withheld') NOT NULL,
PRIMARY KEY (`sem_result_id`),
KEY `idx_semres_student_id` (`student_id`),
KEY `idx_semres_exam_id` (`exam_id`),
CONSTRAINT `fk_semres_exam` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`exam_id`),
CONSTRAINT `fk_semres_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Staff Table
CREATE TABLE `staff` (
`staff_id` int(11) NOT NULL AUTO_INCREMENT,
`college_id` int(11) DEFAULT NULL,
`staff_code` varchar(20) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`email` varchar(255) NOT NULL,
`phone` varchar(15) NOT NULL,
`role` enum('Internal','External','Valuator','Invigilator') NOT NULL,
`status` enum('Active','Inactive') NOT NULL,
PRIMARY KEY (`staff_id`),
UNIQUE KEY `uq_staff_code` (`staff_code`),
UNIQUE KEY `uq_staff_email` (`email`),
KEY `idx_staff_college_id` (`college_id`),
CONSTRAINT `fk_staff_college` FOREIGN KEY (`college_id`) REFERENCES `colleges` (`college_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Staff Privileges Table
CREATE TABLE `staff_privileges` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_id` int(11) NOT NULL,
`permission_id` int(11) NOT NULL,
`is_add` tinyint(1) NOT NULL,
`is_edit` tinyint(1) NOT NULL,
`is_view` tinyint(1) NOT NULL,
`is_delete` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Staff Roles Table
CREATE TABLE `staff_roles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`staff_id` int(11) NOT NULL,
`role_id` int(11) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_staff_role` (`staff_id`,`role_id`),
KEY `idx_staff_roles_staff_id` (`staff_id`),
KEY `idx_staff_roles_role_id` (`role_id`),
KEY `idx_staff_roles_staff` (`staff_id`),
KEY `idx_staff_roles_role` (`role_id`),
CONSTRAINT `fk_staff_roles_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_staff_roles_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Staff Role Types Table
CREATE TABLE `staff_role_types` (
`role_id` int(11) NOT NULL AUTO_INCREMENT,
`role_name` varchar(100) NOT NULL,
`status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
`updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`role_id`),
UNIQUE KEY `role_name` (`role_name`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Students Table
CREATE TABLE `students` (
`student_id` int(11) NOT NULL AUTO_INCREMENT,
`university_rollno` varchar(20) NOT NULL,
`college_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`date_of_joining` date NOT NULL,
`current_semester` int(11) NOT NULL,
`status` enum('Active','Completed','Dropped Out') NOT NULL,
`folio_prefix` varchar(10) DEFAULT NULL COMMENT 'Folio prefix for marksheet generation',
PRIMARY KEY (`student_id`),
UNIQUE KEY `uq_students_rollno` (`university_rollno`),
KEY `idx_students_college_id` (`college_id`),
KEY `idx_students_course_id` (`course_id`),
KEY `idx_students_course_semester` (`course_id`,`current_semester`),
KEY `idx_students_rollno` (`university_rollno`),
CONSTRAINT `fk_students_college` FOREIGN KEY (`college_id`) REFERENCES `colleges` (`college_id`),
CONSTRAINT `fk_students_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`)
) ENGINE=InnoDB AUTO_INCREMENT=179 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Student Certificates Table
CREATE TABLE `student_certificates` (
`certificate_id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`academic_year` varchar(50) NOT NULL,
`semester` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`certificate_number` varchar(50) NOT NULL,
`certificate_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`certificate_data`)),
`status` enum('Generated','Printed','Cancelled') DEFAULT 'Generated',
`generated_by` int(11) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`certificate_id`),
UNIQUE KEY `certificate_number` (`certificate_number`),
KEY `idx_student_academic` (`student_id`,`academic_year`,`semester`),
KEY `idx_certificate_number` (`certificate_number`),
KEY `course_id` (`course_id`),
KEY `generated_by` (`generated_by`),
CONSTRAINT `student_certificates_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE CASCADE,
CONSTRAINT `student_certificates_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON DELETE CASCADE,
CONSTRAINT `student_certificates_ibfk_3` FOREIGN KEY (`generated_by`) REFERENCES `staff` (`staff_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Student Marksheets Table
CREATE TABLE `student_marksheets` (
`marksheet_id` int(11) NOT NULL AUTO_INCREMENT,
`folio_number` varchar(50) NOT NULL,
`student_id` int(11) NOT NULL,
`academic_year` varchar(50) NOT NULL,
`semester` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`generated_date` datetime DEFAULT current_timestamp(),
`generated_by` int(11) DEFAULT NULL,
`status` enum('Generated','Printed','Reissued') DEFAULT 'Generated',
`print_count` int(11) DEFAULT 0,
`last_printed` datetime DEFAULT NULL,
`marksheet_data` longtext DEFAULT NULL COMMENT 'JSON data of marksheet content',
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`marksheet_id`),
UNIQUE KEY `folio_number` (`folio_number`),
UNIQUE KEY `uq_folio_number` (`folio_number`),
KEY `idx_student_academic` (`student_id`,`academic_year`,`semester`),
KEY `idx_course_semester` (`course_id`,`semester`),
KEY `idx_folio_number` (`folio_number`),
KEY `idx_marksheet_search` (`student_id`,`academic_year`,`semester`,`course_id`),
KEY `idx_marksheet_folio` (`folio_number`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Student Marks Entry Table
CREATE TABLE `student_marks_entry` (
`entry_id` int(11) NOT NULL AUTO_INCREMENT,
`session_id` int(11) NOT NULL,
`registration_id` int(11) NOT NULL,
`question_number` int(11) NOT NULL,
`marks_obtained` decimal(5,2) NOT NULL DEFAULT 0.00,
`max_marks` decimal(5,2) NOT NULL,
`valuator_id` int(11) NOT NULL,
`entry_date` timestamp NOT NULL DEFAULT current_timestamp(),
`reviewed_by` int(11) DEFAULT NULL,
`review_date` timestamp NULL DEFAULT NULL,
`status` enum('Entered','Reviewed','Approved','Rejected') NOT NULL DEFAULT 'Entered',
`remarks` text DEFAULT NULL,
PRIMARY KEY (`entry_id`),
UNIQUE KEY `uq_sme_session_registration_question` (`session_id`,`registration_id`,`question_number`),
KEY `idx_sme_session` (`session_id`),
KEY `idx_sme_registration` (`registration_id`),
KEY `idx_sme_valuator` (`valuator_id`),
KEY `idx_sme_reviewed_by` (`reviewed_by`),
CONSTRAINT `fk_sme_registration` FOREIGN KEY (`registration_id`) REFERENCES `student_registrations` (`registration_id`),
CONSTRAINT `fk_sme_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `staff` (`staff_id`),
CONSTRAINT `fk_sme_session` FOREIGN KEY (`session_id`) REFERENCES `mark_entry_sessions` (`session_id`),
CONSTRAINT `fk_sme_valuator` FOREIGN KEY (`valuator_id`) REFERENCES `staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Student Marks Summary Table
CREATE TABLE `student_marks_summary` (
`summary_id` int(11) NOT NULL AUTO_INCREMENT,
`registration_id` int(11) NOT NULL,
`marks_internal` int(11) DEFAULT NULL,
`marks_external` decimal(7,2) NOT NULL,
`marks_total` decimal(7,2) NOT NULL,
`result` enum('Pass','Fail','Absent') NOT NULL DEFAULT 'Absent',
`grade_id` int(11) DEFAULT NULL,
`grade_point` decimal(3,2) DEFAULT NULL,
`attendance_status` enum('Present','Absent') NOT NULL DEFAULT 'Absent',
`is_revaluation_applied` tinyint(1) NOT NULL DEFAULT 0,
`revaluation_marks` decimal(7,2) DEFAULT NULL,
`revaluation_result` enum('Pass','Fail','Unchanged') DEFAULT NULL,
PRIMARY KEY (`summary_id`),
UNIQUE KEY `uq_sms_registration` (`registration_id`),
KEY `idx_sms_grade_id` (`grade_id`),
CONSTRAINT `fk_sms_grade` FOREIGN KEY (`grade_id`) REFERENCES `grades` (`grade_id`),
CONSTRAINT `fk_sms_registration` FOREIGN KEY (`registration_id`) REFERENCES `student_registrations` (`registration_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Student Question Marks Table
CREATE TABLE `student_question_marks` (
`sqm_id` int(11) NOT NULL AUTO_INCREMENT,
`registration_id` int(11) NOT NULL,
`question_number` int(11) NOT NULL,
`marks_obtained` decimal(5,2) NOT NULL,
`co_mapped` varchar(10) DEFAULT NULL,
`valuator_id` int(11) DEFAULT NULL,
PRIMARY KEY (`sqm_id`),
UNIQUE KEY `uq_sqm_registration_question` (`registration_id`,`question_number`),
KEY `idx_sqm_registration_id` (`registration_id`),
KEY `idx_sqm_valuator_id` (`valuator_id`),
CONSTRAINT `fk_sqm_registration` FOREIGN KEY (`registration_id`) REFERENCES `student_registrations` (`registration_id`),
CONSTRAINT `fk_sqm_valuator` FOREIGN KEY (`valuator_id`) REFERENCES `staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Student Registrations Table
CREATE TABLE `student_registrations` (
`registration_id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`exam_subject_id` int(11) NOT NULL,
`fee_structure_id` int(11) DEFAULT NULL,
`is_arrear` tinyint(1) NOT NULL DEFAULT 0,
`fee_amount` decimal(10,2) NOT NULL,
`fee_status` enum('Paid','Pending') NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`registration_id`),
UNIQUE KEY `uq_unique_registration` (`student_id`,`exam_subject_id`),
KEY `idx_sr_student_id` (`student_id`),
KEY `idx_sr_exam_subject_id` (`exam_subject_id`),
KEY `idx_sr_fee_structure_id` (`fee_structure_id`),
KEY `idx_sr_fee_status` (`fee_status`),
KEY `idx_sr_is_arrear` (`is_arrear`),
KEY `idx_sr_student_arrear` (`student_id`,`is_arrear`),
CONSTRAINT `fk_sr_exam_subject` FOREIGN KEY (`exam_subject_id`) REFERENCES `exam_subjects` (`exam_subject_id`),
CONSTRAINT `fk_sr_fee_structure` FOREIGN KEY (`fee_structure_id`) REFERENCES `course_fee_structure` (`fee_structure_id`),
CONSTRAINT `fk_sr_student` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=249 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Student Seating Table
CREATE TABLE `student_seating` (
`seating_id` int(11) NOT NULL AUTO_INCREMENT,
`registration_id` int(11) NOT NULL COMMENT 'Links to student_registrations for student details',
`allocation_id` int(11) NOT NULL COMMENT 'Links to hall_allocations for hall and session info',
`seat_number` varchar(10) NOT NULL COMMENT 'Seat number (e.g., R1-C1-S1, S1, etc.)',
`created_at` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'When seating was assigned',
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'When seating was last updated',
PRIMARY KEY (`seating_id`),
UNIQUE KEY `uq_seat_allocation_seat` (`allocation_id`,`seat_number`) COMMENT 'Prevents duplicate seats in same allocation',
UNIQUE KEY `uq_seat_registration` (`registration_id`) COMMENT 'One seat per registration',
KEY `idx_ss_registration_id` (`registration_id`),
KEY `idx_ss_allocation_id` (`allocation_id`),
KEY `idx_ss_seat_number` (`seat_number`),
CONSTRAINT `fk_ss_allocation` FOREIGN KEY (`allocation_id`) REFERENCES `hall_allocations` (`allocation_id`) ON DELETE CASCADE,
CONSTRAINT `fk_ss_registration` FOREIGN KEY (`registration_id`) REFERENCES `student_registrations` (`registration_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=249 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Individual student seating assignments in halls';

-- Subjects Table
CREATE TABLE `subjects` (
`subject_id` int(11) NOT NULL AUTO_INCREMENT,
`course_id` int(11) NOT NULL,
`subject_code` varchar(20) NOT NULL,
`subject_name` varchar(255) NOT NULL,
`regulation_year` year(4) NOT NULL,
`type` enum('Regular','Practical','Elective','Lab') NOT NULL,
`credits` int(11) NOT NULL,
`is_lab` tinyint(1) NOT NULL DEFAULT 0,
`semester` int(11) NOT NULL,
`part_no` int(11) NOT NULL DEFAULT 1,
`sem_type` enum('Odd','Even') NOT NULL,
PRIMARY KEY (`subject_id`),
KEY `idx_subjects_course_id` (`course_id`),
KEY `idx_subjects_code` (`subject_code`),
KEY `idx_subjects_semester_type` (`semester`,`type`),
KEY `idx_subjects_course_semester` (`course_id`,`semester`),
CONSTRAINT `fk_subjects_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Subject Assessment Settings Table
CREATE TABLE `subject_assessment_settings` (
`setting_id` int(11) NOT NULL AUTO_INCREMENT,
`subject_id` int(11) NOT NULL,
`exam_type` enum('Internal','Semester') NOT NULL,
`max_marks` int(11) NOT NULL,
`pass_marks` int(11) NOT NULL,
`min_marks` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`setting_id`),
KEY `idx_sas_subject_id` (`subject_id`),
CONSTRAINT `fk_sas_subject` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Subject Results Table
CREATE TABLE `subject_results` (
`result_id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`subject_id` int(11) NOT NULL,
`exam_id` int(11) NOT NULL,
`internal_marks` decimal(5,2) NOT NULL,
`external_marks` decimal(5,2) NOT NULL,
`total_marks` decimal(5,2) NOT NULL,
`grade_id` int(11) NOT NULL,
`grade_point` decimal(3,2) NOT NULL,
`result` enum('Pass','Fail') NOT NULL,
PRIMARY KEY (`result_id`),
UNIQUE KEY `uq_subject_result` (`student_id`,`subject_id`,`exam_id`),
KEY `idx_sr_student_id2` (`student_id`),
KEY `idx_sr_subject_id2` (`subject_id`),
KEY `idx_sr_exam_id2` (`exam_id`),
KEY `idx_sr_grade_id2` (`grade_id`),
CONSTRAINT `fk_sr_exam2` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`exam_id`),
CONSTRAINT `fk_sr_grade2` FOREIGN KEY (`grade_id`) REFERENCES `grades` (`grade_id`),
CONSTRAINT `fk_sr_student2` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`),
CONSTRAINT `fk_sr_subject2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tbl Branch Table
CREATE TABLE `tbl_branch` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`branch_code` varchar(50) NOT NULL,
`branch_name` varchar(255) NOT NULL,
`prefix` varchar(10) DEFAULT NULL,
`logo` varchar(255) DEFAULT NULL,
`invoice_logo` varchar(255) DEFAULT NULL,
`address` text NOT NULL,
`city` varchar(100) DEFAULT NULL,
`state` varchar(100) DEFAULT NULL,
`country` varchar(100) DEFAULT NULL,
`postal_code` varchar(20) DEFAULT NULL,
`gst` varchar(50) DEFAULT NULL,
`mobile_number1` varchar(15) NOT NULL,
`mobile_number2` varchar(15) DEFAULT NULL,
`email` varchar(255) NOT NULL,
`alternate_email` varchar(255) DEFAULT NULL,
`timezone` varchar(100) DEFAULT 'UTC',
`remarks` text DEFAULT NULL,
`status` enum('active','inactive') DEFAULT 'active',
`created_by` int(11) NOT NULL,
`updated_by` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `branch_code` (`branch_code`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Tbl Department Table
CREATE TABLE `tbl_department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Tbl Employee Table
CREATE TABLE `tbl_employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`employee_id` int(11) DEFAULT NULL,
`name` varchar(20) NOT NULL,
`age` int(11) NOT NULL,
`designation_id` int(11) NOT NULL,
`role_id` int(11) NOT NULL,
`branch_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
`address` varchar(100) NOT NULL,
`salary` int(11) NOT NULL,
`doj` date NOT NULL COMMENT 'Date of Joining',
`doe` date NOT NULL COMMENT 'Date of Exit',
`role` int(11) NOT NULL,
`email` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`status` enum('active','inactive') DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`created_by` int(11) NOT NULL,
`updated_by` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Tbl Exam Attendance Table
CREATE TABLE `tbl_exam_attendance` (
`attendance_id` int(11) NOT NULL AUTO_INCREMENT,
`registration_id` int(11) NOT NULL,
`timetable_id` int(11) NOT NULL,
`attendance_status` enum('Present','Absent') NOT NULL DEFAULT 'Present',
`marked_by` int(11) DEFAULT NULL,
`marked_at` timestamp NOT NULL DEFAULT current_timestamp(),
`remarks` text DEFAULT NULL,
`is_late` tinyint(1) NOT NULL DEFAULT 0,
`late_minutes` int(11) DEFAULT NULL,
PRIMARY KEY (`attendance_id`),
UNIQUE KEY `uq_attendance_registration` (`registration_id`),
KEY `idx_ea_timetable_id` (`timetable_id`),
KEY `idx_ea_attendance_status` (`attendance_status`),
KEY `idx_ea_marked_by` (`marked_by`),
KEY `idx_ea_marked_at` (`marked_at`),
CONSTRAINT `fk_ea_marked_by` FOREIGN KEY (`marked_by`) REFERENCES `staff` (`staff_id`) ON DELETE SET NULL,
CONSTRAINT `fk_ea_registration` FOREIGN KEY (`registration_id`) REFERENCES `student_registrations` (`registration_id`) ON DELETE CASCADE,
CONSTRAINT `fk_ea_timetable` FOREIGN KEY (`timetable_id`) REFERENCES `exam_timetable` (`timetable_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tbl Settings Table
CREATE TABLE `tbl_settings` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`username` varchar(100) NOT NULL,
`password` varchar(255) NOT NULL,
`status` enum('active','inactive') NOT NULL DEFAULT 'active',
`logo` text NOT NULL,
`name` varchar(255) NOT NULL,
`url` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Tbl State Table
CREATE TABLE `tbl_state` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`status` enum('active','inactive') DEFAULT 'active',
`created_by` int(11) NOT NULL,
`updated_by` int(11) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Tbl User Table
CREATE TABLE `tbl_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`branch_id` int(11) NOT NULL,
`user_id` varchar(50) NOT NULL,
`role_id` int(11) DEFAULT NULL COMMENT '1=>''Super Admin'', 2=>''User'',3=>''Client''',
`username` varchar(100) NOT NULL,
`password` varchar(255) NOT NULL,
`status` enum('active','inactive') NOT NULL DEFAULT 'active',
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`created_by` int(11) NOT NULL,
`updated_by` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- Valuation Duties Table
CREATE TABLE `valuation_duties` (
`duty_id` int(11) NOT NULL AUTO_INCREMENT,
`room_id` int(11) NOT NULL,
`staff_id` int(11) NOT NULL,
`date` date NOT NULL,
`session` enum('FN','AN','FULL') NOT NULL DEFAULT 'FN',
`role` enum('Supervisor','Invigilator') NOT NULL DEFAULT 'Invigilator',
`academic_year` varchar(50) NOT NULL,
`exam_type_id` int(11) NOT NULL,
PRIMARY KEY (`duty_id`),
KEY `idx_vdut_room_date` (`room_id`,`date`,`session`),
KEY `idx_vdut_staff_date` (`staff_id`,`date`,`session`),
CONSTRAINT `fk_vdut_room` FOREIGN KEY (`room_id`) REFERENCES `valuation_rooms` (`room_id`),
CONSTRAINT `fk_vdut_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Valuation Marks Table
CREATE TABLE `valuation_marks` (
`mark_id` int(11) NOT NULL AUTO_INCREMENT,
`registration_id` int(11) NOT NULL,
`exam_subject_id` int(11) NOT NULL,
`staff_id` int(11) DEFAULT NULL,
`total_mark` decimal(6,2) NOT NULL DEFAULT 0.00,
`marks_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`marks_json`)),
`marked_at` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`mark_id`),
UNIQUE KEY `uq_vm_registration` (`registration_id`),
KEY `idx_vm_exam_subject` (`exam_subject_id`),
KEY `idx_vm_staff` (`staff_id`),
CONSTRAINT `fk_vm_exam_subject` FOREIGN KEY (`exam_subject_id`) REFERENCES `exam_subjects` (`exam_subject_id`) ON DELETE CASCADE,
CONSTRAINT `fk_vm_registration` FOREIGN KEY (`registration_id`) REFERENCES `student_registrations` (`registration_id`) ON DELETE CASCADE,
CONSTRAINT `fk_vm_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Valuation Rooms Table
CREATE TABLE `valuation_rooms` (
`room_id` int(11) NOT NULL AUTO_INCREMENT,
`room_name` varchar(100) NOT NULL,
`incharge_id` int(11) NOT NULL,
`capacity` int(11) NOT NULL DEFAULT 600,
PRIMARY KEY (`room_id`),
KEY `idx_vr_incharge_id` (`incharge_id`),
CONSTRAINT `fk_vr_incharge` FOREIGN KEY (`incharge_id`) REFERENCES `staff` (`staff_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Valuation Salary Table
CREATE TABLE `valuation_salary` (
`salary_id` int(11) NOT NULL AUTO_INCREMENT,
`staff_id` int(11) NOT NULL,
`exam_subject_id` int(11) NOT NULL,
`rate_per_script` decimal(10,2) NOT NULL,
`num_scripts` int(11) NOT NULL,
`total_amount` decimal(10,2) NOT NULL,
`payment_status` enum('Pending','Processing','Paid') NOT NULL,
PRIMARY KEY (`salary_id`),
KEY `idx_vs_staff_id` (`staff_id`),
KEY `idx_vs_exam_subject_id` (`exam_subject_id`),
CONSTRAINT `fk_vs_exam_subject` FOREIGN KEY (`exam_subject_id`) REFERENCES `exam_subjects` (`exam_subject_id`),
CONSTRAINT `fk_vs_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Valuation Timetable Table
CREATE TABLE `valuation_timetable` (
`timetable_id` int(11) NOT NULL AUTO_INCREMENT,
`room_id` int(11) NOT NULL,
`date` date NOT NULL,
`session` enum('FN','AN','FULL') NOT NULL DEFAULT 'FN',
`academic_year` varchar(50) NOT NULL,
`exam_type_id` int(11) NOT NULL,
PRIMARY KEY (`timetable_id`),
KEY `idx_vtt_room_date` (`room_id`,`date`),
KEY `idx_vtt_year_type` (`academic_year`,`exam_type_id`),
CONSTRAINT `fk_vtt_room` FOREIGN KEY (`room_id`) REFERENCES `valuation_rooms` (`room_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Valuator Allocation Table
CREATE TABLE `valuator_allocation` (
`allocation_id` int(11) NOT NULL AUTO_INCREMENT,
`exam_subject_id` int(11) NOT NULL,
`valuator_id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`num_scripts` int(11) NOT NULL,
`num_scripts_completed` int(11) NOT NULL DEFAULT 0,
`allocation_start_date` date DEFAULT NULL,
`allocation_end_date` date DEFAULT NULL,
PRIMARY KEY (`allocation_id`),
KEY `idx_va_exam_subject_id` (`exam_subject_id`),
KEY `idx_va_valuator_id` (`valuator_id`),
KEY `idx_va_room_id` (`room_id`),
KEY `idx_va_exam_room_val` (`exam_subject_id`,`room_id`,`valuator_id`),
CONSTRAINT `fk_va_exam_subject` FOREIGN KEY (`exam_subject_id`) REFERENCES `exam_subjects` (`exam_subject_id`),
CONSTRAINT `fk_va_room` FOREIGN KEY (`room_id`) REFERENCES `valuation_rooms` (`room_id`),
CONSTRAINT `fk_va_valuator` FOREIGN KEY (`valuator_id`) REFERENCES `staff` (`staff_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Views;